package com.hssy.wululuaplan.service.impl;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.hssy.wululuaplan.entity.Attendance;
import com.hssy.wululuaplan.entity.DivideClass;
import com.hssy.wululuaplan.entity.Student;
import com.hssy.wululuaplan.exception.CustomException;
import com.hssy.wululuaplan.form.StudentForm;
import com.hssy.wululuaplan.mapper.AttendanceMapper;
import com.hssy.wululuaplan.mapper.DivideClassMapper;
import com.hssy.wululuaplan.mapper.StudentMapper;
import com.hssy.wululuaplan.service.SendMailService;
import com.hssy.wululuaplan.service.StudentService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hssy.wululuaplan.utils.MailRequest;
import com.hssy.wululuaplan.utils.PinyinUtil;
import com.hssy.wululuaplan.utils.mergeCell.EffectColumn;
import com.hssy.wululuaplan.utils.mergeCell.EffectRow;
import com.hssy.wululuaplan.vo.StudentClassVo;
import com.hssy.wululuaplan.vo.StudentDownload2Vo;
import com.hssy.wululuaplan.vo.StudentDownloadVo;
import com.hssy.wululuaplan.vo.StudentVo;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author Mr.Hongtao
 * @since 2024-02-27
 */
@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {
    @Resource
    private DivideClassMapper divideClassMapper;

    @Resource
    private SendMailService sendMailService;

    @Resource
    private AttendanceMapper attendanceMapper;

    @Override
    public Map<String, Object> findStudentListInitial() {
        List<Student> list = baseMapper.selectList(Wrappers.<Student>lambdaQuery().orderByAsc(Student::getInitial, Student::getStudentName));
        List<String> initialList = new ArrayList<>();
        List<List<StudentVo>> columnList = new ArrayList<>();
        List<StudentVo> rowList = new ArrayList<>();
        int index = 0;
        for (Student student : list) {
            index++;
            if (!initialList.contains(student.getInitial())) {
                initialList.add(student.getInitial()); // 添加索引字母列表
                if (!rowList.isEmpty()) {
                    columnList.add(rowList);
                }
                rowList = new ArrayList<>();
            }
            StudentVo vo = new StudentVo();
            BeanUtils.copyProperties(student, vo);
            Date birthday = student.getBirthday();
            if (birthday != null) {
                long age = DateUtil.betweenYear(birthday, new Date(), false);
                vo.setAge((int) age);
            }
            rowList.add(vo);
            if (list.size() == index) {
                columnList.add(rowList);
            }
        }
        Map<String, Object> map = new HashMap<>();
        map.put("initialList", initialList);
        map.put("studentList", columnList);
        return map;
    }

    @Override
    @Transactional
    public void create(StudentForm form) {
        String studentName = form.getStudentName();
        Student exist = baseMapper.selectOne(Wrappers.<Student>lambdaQuery()
                .eq(Student::getStudentName, studentName)
                .last("limit 1"));
        if (exist != null) {
            throw new CustomException(-2, "当前用户已存在");
        }
        // 首字符的拼音
        String initial = PinyinUtil.initial(form.getStudentName());
        Student student = new Student();
        BeanUtils.copyProperties(form, student);
        if (form.getBirthday() != null) {
            DateTime birthday = DateUtil.parse(form.getBirthday());
            student.setBirthday(birthday);
        }
        student.setInitial(initial);
        baseMapper.insert(student);
    }

    @Override
    @Transactional
    public void edit(StudentForm form) {
        Student student1 = baseMapper.selectById(form.getId());
        if (student1 == null) {
            throw new CustomException(-10000, "当前用户不存在");
        }

        Student student = new Student();
        BeanUtils.copyProperties(form, student);

        String studentName = form.getStudentName();
        String initial = PinyinUtil.initial(studentName);
        student.setInitial(initial);
        if (form.getBirthday() != null && !"".equals(form.getBirthday())) {
            DateTime birthday = DateUtil.parse(form.getBirthday(), "yyyy-MM-dd");
            student.setBirthday(birthday);
        }
        baseMapper.updateById(student);
    }

    @Override
    public StudentVo findById(Integer id, String studentName) {
        StudentVo studentVo = new StudentVo();
        Student student;
        if (id != null ){
            student = baseMapper.selectById(id);
        }else{
            student = baseMapper.selectOne(Wrappers.<Student>lambdaQuery()
                    .eq(Student::getStudentName, studentName)
                    .last("limit 1"));
        }
        BeanUtils.copyProperties(student,studentVo);
        if (student.getBirthday() != null) {
            long age = DateUtil.betweenYear(student.getBirthday(), new Date(), false);
            studentVo.setAge((int) age);
        }
        return studentVo;
    }

    @Override
    public List<StudentClassVo> findHasRemainStudentList(Integer courseId) {
        List<DivideClass> list = divideClassMapper.selectList(Wrappers.<DivideClass>lambdaQuery()
                .eq(DivideClass::getCourseId, courseId));
        List<Integer> studentIds = list.stream().map(DivideClass::getStudentId).collect(Collectors.toList());

        List<StudentClassVo> voList = new ArrayList<>();
        if (!studentIds.isEmpty()) {
            List<Student> students = baseMapper.selectBatchIds(studentIds);
            for (Student student : students) {
                StudentClassVo vo = new StudentClassVo();
                BeanUtils.copyProperties(student,vo);
                vo.setChecked(true);
                voList.add(vo);
            }

            List<Student> studentList = baseMapper.findHasRemainStudentList();
            for (Student student : studentList) {
                if (!studentIds.contains(student.getId())){
                    StudentClassVo vo = new StudentClassVo();
                    BeanUtils.copyProperties(student,vo);
                    vo.setChecked(false);
                    voList.add(vo);
                }
            }
        }else {
            List<Student> studentList = baseMapper.findHasRemainStudentList();
            for (Student student : studentList) {
                StudentClassVo vo = new StudentClassVo();
                BeanUtils.copyProperties(student,vo);
                vo.setChecked(false);
                voList.add(vo);
            }
        }



        return voList;
    }

    /**
     * 下载学生列表详细信息
     *
     * @param response HttpServletResponse
     * @param mailTo
     */
    @Override
    public void downloadStudentDetailList(String mailTo) {
        String date = DateUtil.format(new Date(), "yyyyMMddHHmmss");
        String uuid = UUID.randomUUID().toString().substring(0, 5);
        char c = RandomUtil.randomChinese();
        // 查询数据
        List<StudentDownloadVo> list = baseMapper.selectStudentDetailList();
        ExcelWriter writer = ExcelUtil.getWriter("d:/wululu/学生详情数据(" + c + ").xlsx");
        // 给表头设置别名，会按设置顺序输出
        writer.addHeaderAlias("id", "唯一编号");
        writer.addHeaderAlias("studentName", "学生姓名");
        writer.addHeaderAlias("sex", "性别");
        writer.addHeaderAlias("birthday", "生日");
        writer.addHeaderAlias("phone", "电话号码");
        writer.addHeaderAlias("grade", "年级");
        writer.addHeaderAlias("school", "学校");
        writer.addHeaderAlias("address", "家庭住址");
        writer.addHeaderAlias("guardian", "监护人");
        writer.addHeaderAlias("guardianPhone", "监护人电话");
        writer.addHeaderAlias("remark", "备注");
        writer.addHeaderAlias("orderNo", "订单号");
        writer.addHeaderAlias("expenseItem", "收费项目");
        writer.addHeaderAlias("price", "费用");
        writer.addHeaderAlias("remainHour", "剩余课时数");
        // writer.addHeaderAlias("courseName", "课程名称");
        // writer.addHeaderAlias("courseStart", "上课时间");

        // 设置自适应单元格宽度 (测试后无效，后续有空再研究)
        // writer.autoSizeColumnAll();

        // 设置指定列宽
        writer.setColumnWidth(0, 8);
        writer.setColumnWidth(1, 10);
        writer.setColumnWidth(2, 8);
        writer.setColumnWidth(3, 15);
        writer.setColumnWidth(4, 15);
        writer.setColumnWidth(5, 10);
        writer.setColumnWidth(6, 20);
        writer.setColumnWidth(7, 20);
        writer.setColumnWidth(8, 10);
        writer.setColumnWidth(9, 15);
        writer.setColumnWidth(10, 20);
        writer.setColumnWidth(11, 15);
        writer.setColumnWidth(12, 22);
        writer.setColumnWidth(13, 10);
        writer.setColumnWidth(14, 22);
        // writer.setColumnWidth(15, 22);
        // writer.setColumnWidth(16, 22);

        // 设置指定行高
        writer.setRowHeight(0,30);
        // writer.setDefaultRowHeight(20); // 设置默认行高

        // 合并单元格处理
        // 逐一遍历，当前单元格与下一行的单元格数据内容相同，则合并单元格，直到下一个单元格不相同，停止
        List<List<Object>> mergeList = new ArrayList<>(); // 准备待合并的二维数组
        List<EffectRow> effectRows = new ArrayList<>();
        List<EffectColumn> effectColumns; // 受影响的列：可以提前处理
        ArrayList<Integer> notList = new ArrayList<>();
        notList.add(11);notList.add(12);notList.add(13);
        // notList.add(15);notList.add(16);
        effectColumns = doNotMergeColumn(15, notList);




        int start = 1, end = start; // 从excel第二行开始（因为第一行是表头）
        for (int dataRow = 0; dataRow < list.size() - 1; dataRow++) {
            // 从第一行开始（注意这里是数据）
            StudentDownloadVo vo = list.get(dataRow);
            Integer id = vo.getId();
            String studentName = vo.getStudentName();
            String sex = vo.getSex();
            String birthday = vo.getBirthday();
            String phone = vo.getPhone();
            String grade = vo.getGrade();
            String school = vo.getSchool();
            String address = vo.getAddress();
            String guardian = vo.getGuardian();
            String guardianPhone = vo.getGuardianPhone();
            String remark = vo.getRemark();
            Integer remainHour = vo.getRemainHour();
            String orderNo = vo.getOrderNo();
            String expenseItem = vo.getExpenseItem();
            BigDecimal price = vo.getPrice();

            StudentDownloadVo next = list.get(dataRow + 1);
            if (next.getId() != null && next.getId().equals(id)) {
                // 如果下一行的id与当前行相同，则给end变量赋值+1
                end++;
            }else {
                // 否则end变量停止赋值，说明只合并到当前行
                EffectRow er = new EffectRow();
                er.setRowStartIndex(start);
                er.setRowEndIndex(end);
                effectRows.add(er);
                // 待合并的列数据
                List<Object> record = new ArrayList<>();
                record.add(id);             // 合并后的值 第一列 （如下都是需要合并的列）
                record.add(studentName);    // 合并后的值 第二列
                record.add(sex);
                record.add(birthday);
                record.add(phone);
                record.add(grade);
                record.add(school);
                record.add(address);
                record.add(guardian);
                record.add(guardianPhone);
                record.add(remark);
                record.add(remainHour);   // 最后一个需要合并的列
                mergeList.add(record);    // 添加数组到数组中
                // 准备下一轮
                start = dataRow + 2;
                end = start;
            }
        }

        // 合并单元格
        for (int i = 0; i < effectRows.size(); i++) {
            Integer rowStartIndex = effectRows.get(i).getRowStartIndex();
            Integer rowEndIndex = effectRows.get(i).getRowEndIndex();
            if (!rowStartIndex.equals(rowEndIndex)) {
                // 参数说明：起始行、结束行、起始列、结束列
                for (int j = 0; j < effectColumns.size(); j++) {
                    writer.merge(
                            effectRows.get(i).getRowStartIndex(),
                            effectRows.get(i).getRowEndIndex(),
                            effectColumns.get(j).getColStartIndex(),
                            effectColumns.get(j).getColEndIndex(),
                            mergeList.get(i).get(j),
                            null);
                }

            }
        }

        // for (List<Object> record : mergeList) {
        //     // 只有大于1个单元格才能合并
        //     if (!record.get(1).equals(record.get(0))) {
        //         // 合并第二列到第十三列
        //         for (int i = 0; i < 15; i++) {
        //             // 参数说明：起始行、结束行、起始列、结束列
        //             writer.merge(
        //                     effectRows.get(i).getRowStartIndex(),
        //                     effectRows.get(i).getRowEndIndex(),
        //                     effectColumns.get(i).getColStartIndex(),
        //                     effectColumns.get(i).getColEndIndex(),
        //                     record.get(i),
        //                     null);
        //         }
        //     }
        // }

        // 设置sheet名称
        // writer.renameSheet("详情");




        //一次性写出内容，强制输出标题
        writer.write(list, true);
        //关闭writer，释放内存
        writer.close();

        // 发送邮件
        MailRequest mailRequest = new MailRequest();
        // mailRequest.setSendTo("HaiYang.A.He@cisdi.com.cn");
        mailRequest.setSendTo(mailTo != null ? mailTo : "hssymusic@163.com");
        mailRequest.setSubject("【乌鲁咕噜】学生信息");
        mailRequest.setText("【声明】：该文件为内部重要资料，严禁外泄，违者将依法严肃追究法律责任！");
        mailRequest.setFilePath("d:/wululu/学生详情数据(" + c + ").xlsx");
        sendMailService.sendSimpleMail(mailRequest);





        // 响应数据
        // response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        // String fileName;
        // try {
        //     String today = DateUtil.format(DateUtil.date(), "yyyy-MM-dd");
        //     fileName = URLEncoder.encode("学生清单(" + today + ")", StandardCharsets.UTF_8.name());
        // } catch (UnsupportedEncodingException e) {
        //     throw new RuntimeException(e);
        // }
        // response.setHeader("Content-Disposition","attachment;filename*=utf-8''" + fileName + ".xlsx");
        // ServletOutputStream outputStream = null;
        // try {
        //     outputStream = response.getOutputStream();
        //     writer.flush(outputStream,true);
        // } catch (IOException e) {
        //     throw new RuntimeException(e);
        // }finally {
        //     // 关闭writer，释放内存
        //     writer.close();
        //     //此处记得关闭输出Servlet流
        //     IoUtil.close(outputStream);
        // }
    }

    /**
     * 下载指定学生的上课记录
     * @param studentId 学生id
     */
    @Override
    public void emailToStudentClassRecordList(Integer studentId) {
        String date = DateUtil.format(new Date(), "yyyy-MM-dd-HH-mm-ss");
        // 查询数据
        List<StudentDownload2Vo> list = baseMapper.selectStudentClassRecordList(studentId);
        if (list.size() > 0) {
            ExcelWriter writer = ExcelUtil.getWriter("d:/上课记录（" + (studentId == null ? "全部" :list.get(0).getStudentName()) + "）" + date + ".xlsx");
            // 给表头设置别名，会按设置顺序输出
            writer.addHeaderAlias("id", "唯一编号");
            writer.addHeaderAlias("studentName", "学生姓名");
            writer.addHeaderAlias("sex", "性别");
            writer.addHeaderAlias("birthday", "生日");
            writer.addHeaderAlias("courseName", "课程名称");
            writer.addHeaderAlias("courseStart", "上课时间");
            writer.addHeaderAlias("remainHour", "剩余课时数");

            // 设置指定列宽
            writer.setColumnWidth(0, 8);
            writer.setColumnWidth(1, 10);
            writer.setColumnWidth(2, 8);
            writer.setColumnWidth(3, 15);
            writer.setColumnWidth(4, 20);
            writer.setColumnWidth(5, 20);
            writer.setColumnWidth(6, 10);

            // 设置指定行高
            writer.setRowHeight(0,30);

            // 合并单元格处理
            // 逐一遍历，当前单元格与下一行的单元格数据内容相同，则合并单元格，直到下一个单元格不相同，停止
            List<List<Object>> mergeList = new ArrayList<>(); // 准备待合并的二维数组
            List<EffectRow> effectRows = new ArrayList<>();
            List<EffectColumn> effectColumns; // 受影响的列：可以提前处理
            ArrayList<Integer> notList = new ArrayList<>();
            notList.add(4);notList.add(5);
            effectColumns = doNotMergeColumn(7, notList);

            int start = 1, end = start; // 从excel第二行开始（因为第一行是表头）
            for (int dataRow = 0; dataRow < list.size() - 1; dataRow++) {
                // 从第一行开始（注意这里是数据）
                StudentDownload2Vo vo = list.get(dataRow);
                Integer id = vo.getId();
                String studentName = vo.getStudentName();
                String sex = vo.getSex();
                String birthday = vo.getBirthday();
                String courseName = vo.getCourseName();
                Date courseStart = vo.getCourseStart();
                Integer remainHour = vo.getRemainHour();


                StudentDownload2Vo next = list.get(dataRow + 1);
                if (next.getId() != null && next.getId().equals(id)) {
                    // 如果下一行的id与当前行相同，则给end变量赋值+1
                    end++;
                    if (dataRow + 1 == list.size() -1) {
                        // 是否是最后一行，是
                        EffectRow er = new EffectRow();
                        er.setRowStartIndex(start);
                        er.setRowEndIndex(end);
                        effectRows.add(er);
                        // 待合并的列数据
                        List<Object> record = new ArrayList<>();
                        record.add(id);             // 合并后的值 第一列 （如下都是需要合并的列）
                        record.add(studentName);    // 合并后的值 第二列
                        record.add(sex);
                        record.add(birthday);
                        record.add(remainHour);   // 最后一个需要合并的列
                        record.add(courseStart);   // 最后一个需要合并的列
                        record.add(courseName);   // 最后一个需要合并的列
                        mergeList.add(record);    // 添加数组到数组中
                        // 准备下一轮
                        start = dataRow + 2;
                        end = start;
                    }
                }else {
                    // 否则end变量停止赋值，说明只合并到当前行
                    EffectRow er = new EffectRow();
                    er.setRowStartIndex(start);
                    er.setRowEndIndex(end);
                    effectRows.add(er);
                    // 待合并的列数据
                    List<Object> record = new ArrayList<>();
                    record.add(id);             // 合并后的值 第一列 （如下都是需要合并的列）
                    record.add(studentName);    // 合并后的值 第二列
                    record.add(sex);
                    record.add(birthday);
                    record.add(remainHour);   // 最后一个需要合并的列
                    record.add(courseStart);   // 最后一个需要合并的列
                    record.add(courseName);   // 最后一个需要合并的列
                    mergeList.add(record);    // 添加数组到数组中
                    // 准备下一轮
                    start = dataRow + 2;
                    end = start;
                }
            }

            // 合并单元格
            for (int i = 0; i < effectRows.size(); i++) {
                Integer rowStartIndex = effectRows.get(i).getRowStartIndex();
                Integer rowEndIndex = effectRows.get(i).getRowEndIndex();
                if (!rowStartIndex.equals(rowEndIndex)) {
                    // 参数说明：起始行、结束行、起始列、结束列
                    for (int j = 0; j < effectColumns.size(); j++) {
                        writer.merge(
                                effectRows.get(i).getRowStartIndex(),
                                effectRows.get(i).getRowEndIndex(),
                                effectColumns.get(j).getColStartIndex(),
                                effectColumns.get(j).getColEndIndex(),
                                mergeList.get(i).get(j),
                                null);
                    }

                }
            }


            //一次性写出内容，强制输出标题
            writer.write(list, true);
            //关闭writer，释放内存
            writer.close();
        }

    }

    @Override
    public void deleteStudent(Integer id) {
        // 查询该学生是否签到过
        Long count = attendanceMapper.selectCount(
                Wrappers.<Attendance>lambdaQuery()
                        .eq(Attendance::getStudentId, id)
        );
        if (count > 0) {
            throw new CustomException(-3, "有上课记录，无法删除该学生");
        }else {
            baseMapper.deleteById(id);
        }

    }

    /**
     *
     * @param size 总列数
     * @param asList 不合并的列
     * @return
     */
    private List<EffectColumn> doNotMergeColumn(int size, List<Integer> asList) {
        List<EffectColumn> effectColumns = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            if (!asList.contains(i)){
                EffectColumn ec = new EffectColumn();
                ec.setColStartIndex(i);
                ec.setColEndIndex(i);
                effectColumns.add(ec);
            }
        }
        return effectColumns;
    }
}
